//
//  CityListDAO.m
//  ImGuider
//
//  Created by llt on 2017/4/14.
//  Copyright © 2017年 imguider. All rights reserved.
//

#import "CityListDAO.h"
#import "DBHelper.h"

#define TABLE_NAME @"city_list"

#define ID          @"_id" // 本地数据库城市ID
#define CID         @"cid"// 服务器端城市ID
#define CONTINENT   @"continent"
#define KEY_WORDS   @"keywords"
#define JSON        @"JSON"

@implementation CityListDAO

// 创建城市表
+ (BOOL)createMetadataTabWithDB:(FMDatabase *)db {
    
    if ([db tableExists:TABLE_NAME]) {
        [db executeUpdate:[NSString stringWithFormat:@"drop table %@",TABLE_NAME]];
    }
    
    NSString *sqlStr = [NSString stringWithFormat:@"create table %@(%@ integer not null primary key autoincrement,%@ text,%@ text,%@ text,%@ text)",TABLE_NAME,ID,CID,CONTINENT,KEY_WORDS,JSON];
    
    BOOL success = [db executeUpdate:sqlStr];
    
    return success;
}

+ (City *)getCityWithRS:(FMResultSet *)set {
    
    NSString *json = [set stringForColumn:JSON];
    
    City *city = [City yy_modelWithJSON:json];
    city.localID = @([set intForColumn:ID]);
    return city;
}

+ (void)getCityListOrderByNearCity:(City *)city block:(void (^) (NSArray *cityArray))block {
    
    FMDatabaseQueue *queue = [DBHelper getDatabaseQueue];
    
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if ([db open]) {
            
            if (![db tableExists:TABLE_NAME]) {
                [self createMetadataTabWithDB:db];
                return;
            }
            
            FMResultSet *set;
            
            if (!city) {
                
                NSString *sql = [NSString stringWithFormat:@"select * from %@",TABLE_NAME];
                set = [db executeQuery:sql];
            } else {
                
                NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = ?",TABLE_NAME,CONTINENT];
                set = [db executeQuery:sql,city.continent];
            }
            
            NSMutableArray *mArray = [NSMutableArray array];
            
            while ([set next]) {
                
                City *myCity = [self getCityWithRS:set];
            
                if (!city) {
                    [mArray addObject:myCity];
                    continue;
                }
                
                if (myCity.cid.integerValue == city.cid.integerValue) continue; // 去除自己
                
                CLLocationCoordinate2D coordinate = CLLocationCoordinate2DMake(city.lat.floatValue, city.lng.floatValue);
                CLLocationCoordinate2D myCoordinate = CLLocationCoordinate2DMake(myCity.lat.floatValue, myCity.lng.floatValue);
                
                CGFloat distance = [IGUtils getDistanceFromCoordinate:coordinate toCoordinate:myCoordinate];
                
                myCity.distance = @(distance);
                
                [mArray addObject:myCity];
            }
            
            NSArray *sortArray = [mArray sortedArrayUsingComparator:^NSComparisonResult(City *obj1, City *obj2) {
                return [obj1.distance compare:obj2.distance];
            }];
            
            NSMutableArray *countryCityArray = [NSMutableArray array];
            NSMutableArray *notCountryCityArray = [NSMutableArray array];
            
            for (City *cc in sortArray) {
                
                if ([cc.country isEqualToString:city.country]) {
                    [countryCityArray addObject:cc];
                } else {
                    [notCountryCityArray addObject:cc];
                }
            }
            
            
            [countryCityArray addObjectsFromArray:notCountryCityArray];
            
            NSArray *resultArray = countryCityArray;
            if (countryCityArray.count >= 7) {
                resultArray = [countryCityArray subarrayWithRange:NSMakeRange(0, 7)];
            }
            if (block) {
                block(resultArray);
            }
            [set close];
        }
        
    }];
}

+ (void)insertCityArray:(NSArray *)cityArray success:(BOOL *)success {
    
    FMDatabaseQueue *queue = [DBHelper getDatabaseQueue];
    
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if ([db open]) {
            
            [self createMetadataTabWithDB:db];
            
            for (City *city in cityArray) {
                
                NSString *json = [city yy_modelToJSONString];
                
                NSString *sql = [NSString stringWithFormat:@"insert into %@(%@,%@,%@,%@) values (?,?,?,?)",TABLE_NAME,CID,CONTINENT,KEY_WORDS,JSON];

                *success = [db executeUpdate:sql,city.cid,city.continent,city.keywords,json];
                
                if (!success) {
                    IGLog(@"----------------数据库插入错误-------------");
                    *rollback = YES;
                    return;
                }
            }
        }
        
    }];
}


+ (void)getCityListWithMatch:(NSString *)match block:(void (^) (NSArray *cityArray))block {
    
    FMDatabaseQueue *queue = [DBHelper getDatabaseQueue];
    
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if ([db open]) {
            
            if (![db tableExists:TABLE_NAME]) {
                [self createMetadataTabWithDB:db];
                return;
            }
            
            NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ like '%%%@%%'",TABLE_NAME,KEY_WORDS,match];
            FMResultSet *set = [db executeQuery:sql];
            
            NSMutableArray *mArray = [NSMutableArray array];
            
            while ([set next]) {
                
                City *myCity = [self getCityWithRS:set];
                
                [mArray addObject:myCity];
            }
            
            if (block) {
                block(mArray);
            }
            [set close];
        }
        
    }];
}

+ (void)getCityWithCID:(NSString *)cid Block:(void (^) (City *city))block {
    
    FMDatabaseQueue *queue = [DBHelper getDatabaseQueue];
    
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        
        if ([db open]) {
            
            if (![db tableExists:TABLE_NAME]) {
                [self createMetadataTabWithDB:db];
                return;
            }
            
        }
        
        NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = ?",TABLE_NAME,CID];
        FMResultSet *set = [db executeQuery:sql,cid];
        
        City *city = nil;
        if ([set next]) {
            city = [self getCityWithRS:set];
        }
        
        if (block) {
            block(city);
        }
    }];

}
@end
